#!/usr/bin/python3

import psycopg2
import time
conn = psycopg2.connect(database="postgres", user="postgres", password="1", host="127.0.0.1", port="5432")
print("Opened database successfully")
cursor = conn.cursor()
#cursor.execute("delete from COMPANY where id=1")
print("Delete data successfully")
#cursor.execute("select NAME,AGE,ADDRESS,SALARY from COMPANY ")
#cursor.execute("select setval('students1_id_seq', max(id)) from students1")  #插入ID自动加1，但是得先删除主键

def timer(fn):
    def _wrapper(count):
        start = time.time()
        fn(count)
        seconds = time.time() - start
        print(u"{func}函数每 {count} 条数数据写入耗时 {sec}秒".format(func=fn, count=count, sec=seconds))
    return _wrapper
# 普通写入
@timer
def ordinary_insert(count):
    sql = "insert into students1 (name, age, sex,id,cellphone,address,score) values ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66')"
    for i in range(count):
        cursor.execute(sql)
# 批量处理
@timer
def many_insert(count):
    sql = "insert into students1 (name, age, sex,id,cellphone,address,score)values(%s,%s,%s,%s,%s,%s,%s)"

    loop = count / 20
    stus = (('tom666','66','boy','10066','13900000066','shanghai','66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'),
            ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66'),('tom666','66','boy','10066','13900000066','shanghai','66'))

    for i in range(int(loop)):
        cursor.executemany(sql, stus)
# 事务处理
@timer
def transaction_insert(count):
    sql = "insert into students1 (name, age, sex,id,cellphone,address,score)values(%s,%s,%s,%s,%s,%s,%s)"

    stus = ('tom666', '66', 'boy', '10066', '13900000066', 'shanghai', '66')

    if count > 0:
        try:
            for i in range(count):
                cursor.execute(sql, stus)
        except Exception as e:
            conn.rollback()  # 事务回滚
            print('事务处理失败', e)
        else:
            conn.commit()  # 事务提交
            print('事务处理成功, 关闭连接', cursor.rowcount)
           # cursor.close()
           # conn.close()
    else:
        print("输入的count有问题，无法执行数据库操作！")
def test_insert(count):
    ordinary_insert(count)  #执行但不插入数据
   # many_insert(count)
#    transaction_insert(count)   #既执行又插入数据
test_insert(200000)   #插入的数据



#cursor = conn.cursor()
#cursor.execute('''CREATE TABLE COMPANY
    #   (ID INT PRIMARY KEY     NOT NULL,
   #    NAME           TEXT    NOT NULL,
  #     AGE            INT     NOT NULL,
 #      ADDRESS        CHAR(50),
#       SALARY         REAL);''')
#print ("Table created successfully")
#cursor.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
 #     VALUES (1, 'Paul', 32, 'California', 20000.00 )");

#cursor.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
 #     VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

#cursor.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
 #     VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
#
#cursor.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

#conn.commit()
#print("Records created successfully");



#显示数据，下面#打开即可
#cursor.execute("select name, age, sex,id,cellphone,address,score from students1")
while True:
        rows = cursor.fetchall()
        if not rows:
            break
        for row in rows:
            #print('id=', row['id'], ',name=', row['name'], ',pwd=', row['pwd'], ',singal=', row['singal'],)
            name, age, sex,id,cellphone,address,score = row
            print(name, age, sex,id,cellphone,address,score)
conn.close()